﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;
using System.Text.RegularExpressions;

namespace Mefisto4.DataAccess.Helpers
{
    /// <summary>
    /// Mini-ORM, allowing easy dataaccess and automapping for SQL CE
    /// <remarks>
    /// With an entity declared like :
    ///     MyEntity (string prop1, int prop2, decimal prop3)
    /// You can define some statement like :
    ///     "select t1.prop1, t1.prop2, t2.prop1 as prop3 from t1, t2" => automapping get
    /// or like :    
    ///     "select t1.prop1, t1.prop2, t2.prop1 as prop3 from t1, t2 where t2.prop1 = @prop3" => automapping get/set
    /// </remarks>
    /// </summary>
    public class Mefisto4DataAccessSqlCE : IDisposable
    {
        #region Properties

        private readonly SqlCeConnection _connection;

        #endregion

        #region Constructor

        public Mefisto4DataAccessSqlCE()
        {
            _connection = new SqlCeConnection(ConfigurationManager.ConnectionStrings["Mefisto4Db"].ConnectionString);
            _connection.Open();
        }

        #endregion

        #region IDisposable Method

        public void Dispose()
        {
            _connection.Close();
            _connection.Dispose();
        }

        #endregion

        #region CRUD Methods

        public void CreateEntity<T>(string createStatement, T entity) where T : new()
        {
            if (createStatement.Contains("@"))
                createStatement = ProvideParameters(createStatement, entity);

            var command = new SqlCeCommand(createStatement, _connection);
            command.ExecuteNonQuery();

            command.CommandText = "select scope_identity()";
            SetEntityKeyValue(entity, command.ExecuteScalar());
        }

        public T ReadEntity<T>(string readStatement, T parameterEntity) where T : new()
        {
            if (readStatement.Contains("@"))
                readStatement = ProvideParameters(readStatement, parameterEntity);

            var command = new SqlCeCommand(readStatement, _connection);
            return FillEntity<T>(command.ExecuteReader(CommandBehavior.SingleRow));
        }

        public IEnumerable<T> ReadEntities<T>(string readStatement, T parameterEntity) where T : new()
        {
            if (readStatement.Contains("@"))
                readStatement = ProvideParameters(readStatement, parameterEntity);

            var command = new SqlCeCommand(readStatement, _connection);
            return FillEntities<T>(command.ExecuteReader());
        }

        public void UpdateEntity<T>(string updateStatement, T entity) where T : new()
        {
            if (updateStatement.Contains("@"))
                updateStatement = ProvideParameters(updateStatement, entity);

            var command = new SqlCeCommand(updateStatement, _connection);
            command.ExecuteNonQuery();
        }

        public void DeleteEntity<T>(string deleteStatement, T entity) where T : new()
        {
            if (deleteStatement.Contains("@"))
                deleteStatement = ProvideParameters(deleteStatement, entity);

            var command = new SqlCeCommand(deleteStatement, _connection);
            command.ExecuteNonQuery();
        }

        #endregion

        #region Db Methods

        public object GetFromDb<T>(string getStatement, T parameterEntity) where T : new()
        {
            if (getStatement.Contains("@"))
                getStatement = ProvideParameters(getStatement, parameterEntity);

            var command = new SqlCeCommand(getStatement, _connection);
            return command.ExecuteReader(CommandBehavior.SingleResult);
        }

        public void CallOnDb<T>(string statement, T parameterEntity) where T : new()
        {
            if (statement.Contains("@"))
                statement = ProvideParameters(statement, parameterEntity);

            var command = new SqlCeCommand(statement, _connection);
            command.ExecuteNonQuery();
        }

        #endregion

        #region Internal Methods

        internal object GetEntityKeyValue<T>(T entity) where T : new()
        {
            return typeof(T).GetProperty("Id").GetValue(entity, null);
        }

        internal void SetEntityKeyValue<T>(T entity, object value) where T : new()
        {
            typeof(T).GetProperty("Id").SetValue(entity, value, null);
        }

        internal T FillEntity<T>(SqlCeDataReader reader) where T : new()
        {
            var entity = new T();

            for (var i = 0; i < reader.FieldCount; i++)
            {
                typeof(T).GetProperty(reader.GetName(i)).SetValue(entity, reader[i], null);
            }

            return entity;
        }

        internal List<T> FillEntities<T>(SqlCeDataReader reader) where T : new()
        {
            // SQL Server CE ne prend pas en charge HasRows...
            //if (!reader.HasRows)
            //    return new List<T>();

            var entities = new List<T>();

            while (reader.Read())
            {
                var entity = new T();

                for (var i = 0; i < reader.FieldCount; i++)
                {
                    //On n'affecte la valeur que si elle n'est pas nulle
                    if (!reader.IsDBNull(i))
                        typeof(T).GetProperty(reader.GetName(i)).SetValue(entity, reader[i], null);
                }

                entities.Add(entity);
            }

            return entities;
        }

        internal string ProvideParameters<T>(string statement, T entity) where T : new()
        {
            var myParameters = new Regex(@"[\s=]@(\w+)\s");
            return myParameters.Replace(statement, match => typeof(T).GetProperty(match.ToString()).GetValue(entity, null).ToString());
        }

        #endregion
    }
}